Release 10.1A: OpenEdge Data Management:
SQL Reference

GRANT

Grants various privileges to the specified users of the database. There are two forms of the GRANT statement:

Grant database-wide privileges, such as system administration (DBA), general creation (RESOURCE), audit administration (AUDIT_ADMIN), audit archive (AUDIT_ARCHIVE), or audit insert (AUDIT_INSERT).
Grant various privileges on specific tables and views. Privilege definitions are stored in the system tables SYSDBAUTH, SYSTABAUTH, and SYSCOLAUTH for the database, tables, and columns, respectively.

This is the syntax to GRANT database-wide privileges:

Syntax

GRANT { RESOURCE, DBA, AUDIT_ADMIN, AUDIT_ARCHIVE, AUDIT_INSERT }     TO username [ , username ] , ...     [ WITH GRANT OPTION ];

Example

In this example, audit administration and audit archive privileges are granted to bsmith:

GRANT AUDIT_ADMIN, AUDIT_ARCHIVE TO bsmith WITH GRANT OPTION;

Because these privileges are granted to bsmith WITH GRANT OPTION, bsmith may now grant these two privileges to other users.

This is the syntax to GRANT privileges on specific tables and views:

Syntax

GRANT { privilege [, privilege ] , ... | ALL [ PRIVILEGES] } ON table_name TO { username [, username ] , ... | PUBLIC } [ WITH GRANT OPTION ] ;

This is the syntax for the privilege variable:

Syntax

{ SELECT | INSERT | DELETE | INDEX    | UPDATE [ ( column , column , ... ) ]    | REFERENCES [ ( column , column , ... ) ] }

Use the following syntax to assign sequence privileges:

SYNTAX

GRANT [SELECT | UPDATE] ON SEQUENCE schema.sequence TO user_name [,user_name]...

SELECT

Allows specified user to read data from the sequence.

UPDATE

Allows specified user to modify data for the sequence.

Example

In this example, the sequence generator grants user slsadmin the ability to modify the customer number sequence.

GRANT UPDATE      ON SEQUENCE pub.customer_sequence      TO slsadmin;

The following syntax is a variation on the GRANT statement that enables the user to execute stored Java procedures:

Syntax

GRANT EXECUTE ON StoredJavaProcedureName () TO { username [, username ] , ... | PUBLIC } [ WITH GRANT OPTION ] ;

RESOURCE

Allows the specified users to issue CREATE statements.

DBA

Allows the specified users to create, access, modify, or delete any database object, and to grant other users any privileges.

TO username [ , username ] , ...

Grants the specified privileges on the table or view to the specified list of users.

SELECT

Allows the specified users to read data from the table or view.

INSERT

Allows the specified users to add new rows to the table or view.

DELETE

Allows the specified users to delete rows from the table or view.

INDEX

Allows the specified users to create an index on the table or view.

UPDATE [ ( column , column , ... ) ]

Allows the specified users to modify existing rows in the table or view. If followed by a column list, the users can modify values only in the columns named.

REFERENCES [ ( column , column , ... ) ]

Allows the specified users to refer to the table from other tables’ constraint definitions. If followed by a column list, constraint definitions can refer only to the columns named.

Note: For more detail on constraint definitions, see the Column constraints and Table constraints entries of this section.

ALL

Grants all privileges for the table or view.

TO PUBLIC

Grants the specified privileges on the table or view to any user with access to the system.

WITH GRANT OPTION

Allows the specified users to grant their privileges or a subset of their privileges to other users.

Example

GRANT DELETE ON cust_view TO dbuser1 ; GRANT SELECT ON newcustomers TO dbuser2 ;

Note: If the username specified in a RESOURCE or DBA GRANT operation does not already exist, the GRANT statement creates a row in the SYSDBAUTH system table for the new username. This row is not deleted by a subsequent REVOKE operation.

Authorization

Must have the DBA privilege, ownership of the table, or all the specified privileges on the table (granted with the WITH GRANT OPTION clause). Must have the DBA privilege or AUDIT_ADMIN WITH GRANT privilege to grant auditing privileges.

Related statements

REVOKE